/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.xeerkat.xmpp.server.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import org.milowski.db.DB;
import org.milowski.db.DBConnection;
import org.milowski.db.DBIterator;
import org.milowski.db.DBQueryHandler;
import org.milowski.db.DBResultConstructor;
import org.milowski.db.DBUpdateHandler;
import org.milowski.db.Slot;
import org.xeerkat.xmpp.ID;

/**
 *
 * @author alex
 */
public class XMPPDB extends DB {

   public XMPPDB(String location)
   {
      super("xmpp",location);
      getSchemaMap().put("xmpp",XMPPDB.class.getResource("schema.sql"));
      setDriverClass("org.apache.derby.jdbc.EmbeddedDriver");
   }
   
   static final int STATEMENT_GET_USER                   = 100;
   static final int STATEMENT_GET_USER_BY_XMPPID         = 101;
   static final int STATEMENT_CREATE_USER                = 102;
   static final int STATEMENT_LAST_ID_FROM_USERS         = 103;
   static final int STATEMENT_GET_CONTACT                = 104;
   static final int STATEMENT_OFFLINE                    = 105;
   static final int STATEMENT_DELETE_USER_CONTACT_GROUPS = 106;
   static final int STATEMENT_DELETE_USER_GROUPS         = 107;
   static final int STATEMENT_DELETE_USER_CONTACTS       = 108;
   static final int STATEMENT_DELETE_USER                = 109;
   static final int STATEMENT_GET_USER_CONTACT           = 110;
   static final int STATEMENT_CREATE_USER_CONTACT        = 111;
   static final int STATEMENT_LAST_ID_FROM_CONTACTS      = 112;
   static final int STATEMENT_GET_USER_GROUP             = 113;
   static final int STATEMENT_CREATE_USER_GROUP          = 114;
   static final int STATEMENT_LAST_ID_FROM_GROUPS        = 115;
   static final int STATEMENT_DELETE_EMPTY_USER_GROUPS   = 116;
   static final int STATEMENT_IS_ONLINE                  = 117;
   static final int STATEMENT_GO_ONLINE                  = 118;
   static final int STATEMENT_GO_OFFLINE                 = 119;
   static final int STATEMENT_ONLINE_RESOURCES           = 120;
   static final int STATEMENT_USER_CONTACTS              = 121;
   static final int STATEMENT_USER_CONTACTS_BY_SUBSCRIPTION = 122;
   static final int STATEMENT_USER_CONTACTS_BY_ASK          = 123;
   static final int STATEMENT_DELETE_GROUP_CONTACT_GROUPS   = 124;
   static final int STATEMENT_DELETE_GROUP                  = 125;  
   static final int STATEMENT_DELETE_CONTACT_CONTACT_GROUPS = 126;
   static final int STATEMENT_DELETE_CONTACT                = 127;
   static final int STATEMENT_UPDATE_CONTACT                = 128;
   static final int STATEMENT_ADD_CONTACT_TO_GROUP          = 129;
   static final int STATEMENT_DELETE_CONTACT_FROM_GROUP     = 130;
   static final int STATEMENT_DELETE_CONTACT_FROM_ALL_GROUPS = 131;
   static final int STATEMENT_CONTACT_GROUP_NAMES            = 132;
   static final int STATEMENT_GET_USERS                      = 133;
   static final int STATEMENT_USER_ONLINE_RESOURCES          = 134;
   
   protected void prepare(DBConnection connection) 
      throws SQLException
   {
      connection.addStatement(STATEMENT_GET_USER, "select xmppid from xmpp.users where id=?");
      connection.addStatement(STATEMENT_GET_USER_BY_XMPPID, "select id from xmpp.users where xmppid=?");
      connection.addStatement(STATEMENT_CREATE_USER,"insert into xmpp.users (xmppid) values (?)");
      connection.addStatement(STATEMENT_LAST_ID_FROM_USERS,"select identity_val_local() from xmpp.users");
      connection.addStatement(STATEMENT_GET_CONTACT, "select user_ref,xmppid,subscription,ask,name from xmpp.contacts where user_ref=? and xmppid=?");
      connection.addStatement(STATEMENT_OFFLINE, "delete from xmpp.resources");
      connection.addStatement(STATEMENT_DELETE_USER_CONTACT_GROUPS,"delete from xmpp.contact_groups where user_ref=?");
      connection.addStatement(STATEMENT_DELETE_USER_GROUPS,"delete from xmpp.groups where user_ref=?");
      connection.addStatement(STATEMENT_DELETE_USER_CONTACTS,"delete from xmpp.contacts where user_ref=?");
      connection.addStatement(STATEMENT_DELETE_USER,"delete from xmpp.contacts where id=?");
      connection.addStatement(STATEMENT_GET_USER_CONTACT,"select id,subscription,ask,name from xmpp.contacts where user_ref=? and xmppid=?");
      connection.addStatement(STATEMENT_CREATE_USER_CONTACT,"insert into xmpp.contacts (user_ref,xmppid,subscription,ask) values (?,?,?,?)");
      connection.addStatement(STATEMENT_LAST_ID_FROM_CONTACTS,"select identity_val_local() from xmpp.contacts");
      connection.addStatement(STATEMENT_GET_USER_GROUP,"select id from xmpp.groups where user_ref=? and name=?");
      connection.addStatement(STATEMENT_CREATE_USER_GROUP,"insert into xmpp.groups (user_ref,name) values (?,?)");
      connection.addStatement(STATEMENT_LAST_ID_FROM_GROUPS,"select identity_val_local() from xmpp.groups");
      connection.addStatement(STATEMENT_DELETE_EMPTY_USER_GROUPS,"delete from xmpp.groups g where g.user_ref=? and not exists (select group_ref from xmpp.contact_groups c where c.group_ref=g.id)");
      connection.addStatement(STATEMENT_IS_ONLINE,"select id from xmpp.resources where xmppid=?");
      connection.addStatement(STATEMENT_GO_ONLINE,"insert into xmpp.resources (user_ref,xmppid) values (?,?)");
      connection.addStatement(STATEMENT_GO_OFFLINE, "delete from xmpp.resources where user_ref=? and xmppid=?");
      connection.addStatement(STATEMENT_ONLINE_RESOURCES,"select xmppid from xmpp.resources");
      connection.addStatement(STATEMENT_USER_CONTACTS,"select id,xmppid,subscription,ask,name from xmpp.contacts where user_ref=?");
      connection.addStatement(STATEMENT_USER_CONTACTS_BY_SUBSCRIPTION,"select xmppid from xmpp.contacts where user_ref=? and subscription=?");
      connection.addStatement(STATEMENT_USER_CONTACTS_BY_ASK,"select xmppid from xmpp.contacts where user_ref=? and ask=?");
      connection.addStatement(STATEMENT_DELETE_GROUP_CONTACT_GROUPS,"delete from xmpp.contact_groups where group_ref=?");
      connection.addStatement(STATEMENT_DELETE_GROUP,"delete from xmpp.groups where id=?");
      connection.addStatement(STATEMENT_DELETE_CONTACT_CONTACT_GROUPS,"delete from xmpp.contact_groups where contact_ref=?");
      connection.addStatement(STATEMENT_DELETE_CONTACT,"delete from xmpp.contacts where id=?");
      connection.addStatement(STATEMENT_UPDATE_CONTACT,"update xmpp.contacts set subscription=?,ask=?,name=? where id=?");
      connection.addStatement(STATEMENT_ADD_CONTACT_TO_GROUP,"insert into xmpp.contact_groups (user_ref,contact_ref,group_ref) values (?,?,?)");
      connection.addStatement(STATEMENT_DELETE_CONTACT_FROM_GROUP,"delete from xmpp.contact_groups where contact_ref=? and group_ref=?");
      connection.addStatement(STATEMENT_DELETE_CONTACT_FROM_ALL_GROUPS,"delete from xmpp.contact_groups where contact_ref=?");
      connection.addStatement(STATEMENT_CONTACT_GROUP_NAMES,"select g.name from xmpp.contact_groups c,xmpp.groups g where c.contact_ref=? and c.group_ref=g.id");
      connection.addStatement(STATEMENT_GET_USERS, "select id,xmppid from xmpp.users");
      connection.addStatement(STATEMENT_USER_ONLINE_RESOURCES,"select xmppid from xmpp.resources where user_ref=?");
   }
   
   public User getUser(final int id)
      throws SQLException
   {
      final Slot<User> user = new Slot<User>();
      DBConnection connection = getConnection();
      try {
         connection.query(STATEMENT_GET_USER, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setInt(1,id);
            }
            public void onResults(ResultSet set) 
               throws SQLException
            {
               if (set.next()) {
                  user.set(new User(XMPPDB.this,id,new ID(set.getString(1))));
               }
            }
         });
      } finally {
         release(connection);
      }
      return user.get();
   }
   
   public User getUser(final ID id)
      throws SQLException
   {
      final Slot<User> user = new Slot<User>();
      DBConnection connection = getConnection();
      try {
         connection.query(STATEMENT_GET_USER_BY_XMPPID, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setString(1,id.toXMPPId());
            }
            public void onResults(ResultSet set) 
               throws SQLException
            {
               if (set.next()) {
                  user.set(new User(XMPPDB.this,set.getInt(1),new ID(id.toXMPPId())));
               }
            }
         });
      } finally {
         release(connection);
      }
      return user.get();
   }
   
   public User createUser(final ID id)
      throws SQLException
   {
      String xmppId = id.toXMPPId();
      DBConnection connection = getConnection();
      try {
         int dbid = connection.create(STATEMENT_CREATE_USER,STATEMENT_LAST_ID_FROM_USERS,new DBUpdateHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setString(1,id.toXMPPId());
            }
         });
         return new User(this,dbid,new ID(xmppId));
      } finally {
         release(connection);
      }
   }
   
   public Contact getContact(final int dbid)
      throws SQLException
   {
      final Slot<Contact> contact = new Slot<Contact>();
      DBConnection connection = getConnection();
      try {
         connection.query(STATEMENT_GET_USER, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setInt(1,dbid);
            }
            public void onResults(ResultSet set) 
               throws SQLException
            {
               if (set.next()) {
                  User user = getUser(set.getInt(1));
                  contact.set(new Contact(XMPPDB.this,dbid,user,new ID(set.getString(2)),Contact.Subscription.fromInt(set.getInt(3)),Contact.Ask.fromInt(set.getInt(4)),set.getString(5)));
               }
            }
         });
      } finally {
         release(connection);
      }
      return contact.get();
   }
   
   public void offline()
      throws SQLException
   {
      DBConnection connection = getConnection();
      try {
         connection.update(STATEMENT_OFFLINE, new DBUpdateHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
            }
         });
      } finally {
         release(connection);
      }
   }
   
   public boolean isOnline(final ID fullid)
      throws SQLException
   {
      final Slot<Boolean> exists = new Slot<Boolean>(false);
      DBConnection connection = getConnection();
      try {
         connection.query(XMPPDB.STATEMENT_IS_ONLINE, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setString(1,fullid.toString());
            }
            public void onResults(ResultSet set)
               throws SQLException
            {
               exists.set(set.next());  
            }
         });
      } finally {
         release(connection);
      }
      return exists.get();
   }
   
   public Iterator<User> getUsers() 
      throws SQLException
   {
      final Slot<DBIterator<User>> result = new Slot<DBIterator<User>>();
      
      final DBConnection connection = getConnection();
      try {
         connection.query(XMPPDB.STATEMENT_GET_USERS, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
            }
            public boolean shouldClose() {
               return false;
            }
            public void onResults(ResultSet set)
            {
               result.set(new DBIterator<User>(set,new DBResultConstructor<User>() {
                  public User newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return new User(XMPPDB.this,set.getInt(1),new ID(set.getString(2)));
                  }
               },XMPPDB.this,connection));
            }
         });
         return result.get();
      } catch(SQLException ex) {
         release(connection);
         throw ex;
      }
   }
   public Iterator<ID> getResources() 
      throws SQLException
   {
      final Slot<DBIterator<ID>> result = new Slot<DBIterator<ID>>();
      final DBConnection connection = getConnection();
      try {
         connection.query(XMPPDB.STATEMENT_ONLINE_RESOURCES, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
            }
            public boolean shouldClose() {
               return false;
            }
            public void onResults(ResultSet set)
            {
               result.set(new DBIterator<ID>(set,new DBResultConstructor<ID>() {
                  public ID newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return new ID(set.getString(1));
                  }
               },XMPPDB.this,connection));
            }
         });
         return result.get();
      } catch(SQLException ex) {
         release(connection);
         throw ex;
      }
   }
   
}
